<?php
/**
 * This file contains the model view control classes
 *
 * PHP version 5
 *
 * @category  PHP
 * @package   PHPMY
 * @author    Sven Ruttloff, www.sruttloff.de <service@sruttloff.de>
 * @copyright 2010 Sven Ruttloff, www.sruttloff.de
 * @license   http://www.sruttloff.de/cms/files/licence_en_lgpl.txt LGPL
 * @version   SVN: $Id: mvc.class.php 120 2010-08-20 08:46:25Z sruttloff@googlemail.com $
 * @link      phpmy
 */

/**
 * model view control quick and easy
 *
 * @category  PHP
 * @package   PHPMY
 * @author    Sven Ruttloff, www.sruttloff.de <service@sruttloff.de>
 * @copyright 2010 Sven Ruttloff, www.sruttloff.de
 * @license   http://www.sruttloff.de/cms/files/licence_en_lgpl.txt LGPL
 * @link      phpmy
 */
class mvc
{
    /**
     * @var  string  unique mvc object name
     */
    public $MVCNAME = "unique";
    /**
     * @var  object  db table properties
     */
    public $TABLEPROPERTIES = null;
    /**
     * @var  array  array with dataset
     */
    public $DATASET = array ();
    /**
     * @var  object  db handle
     */
    public $DB = null;
    /**
     * @var string name of DB table
     */
    public $TABLE = null;
    /**
     * @var string where filter for db query
     */
    public $FILTER = null;
    /**
     * @var  int   display quantity
     */
    public $DISPLAYLIMIT = 25;
    /**
     * @var  string  group by sql statement
     */
    protected $GROUPBY = null;
    /**
     * @var  int   display offset
     */
    public $VIEWOFFSET = 0;
    /**
     * @var  int   total possible quantity of datas
     */
    public $VIEWDATASTOTAL = null;
    /**
     * @var  array  columns in db table
     */
    protected $DBTABLECOLUMNS = array ();
    /**
     * @var  array  beautified column names
     */
    public $BEAUTYDBTABLECOLUMNS = array ();
    /**
     * @var  string  order by column name(s)
     */
    public $ORDERBYCOLUMN = null;
    /**
     * @var  string  order by direction
     */
    public $ORDERBYDIRECTION = "ASC";
    /**
     * @var  string  Output of dataset view
     */
    public $VIEWDATASETOUTPUT = null;
    /**
     * @var   array  assoc. column name and function to call for
     */
    public $VIEWCOLUMNHANDLERFUNCTIONS = array ();
    /**
     * @var   string  href target for the view
     */
    public $VIEWHREF = "";
    /**
     * @var   array  manual added cols
     */
    public $APPENDCOLUMNS = array ();
    /**
     * @var   array  manual added cols
     */
    public $PREPENDCOLUMNS = array ();
    /**
     * @var   array  dont display this cols
     */
    public $VIEWDONTSHOWCOLUMNS = array ();
    /**
     * @var   array  dont sort this cols by a href
     */
    public $VIEWDONTSORTCOLUMNS = array ();
    /**
     * @var   array  glue some to table head's'
     */
    public $VIEWGLUETOCOLUMNS = array();
    /**
     * @var   bool  is dataset fetched marker
     */
    public $VIEWFETCHMARKER = false;
    /**
     * @var   array  rows to highlight
     */
    public $HIGHLIGHTROW = array ();
    /**
     * @var     object      i18n object
     */
    public $I18N = null;
    /**
     * @var bool if true, no browse line in datatable
     */
    protected $VIEWNOFOOT = false;
    /**
     * get from constructor !
     *
     * @var string table start
     */
    protected $VIEWTABLESTART = '<table class="datatable"
        width="100%" cellspacing="0" cellpadding="1" id="mvc:#:MVCID:#:">';
    protected $VIEWTABLEEND = '</table>';
    protected $VIEWTABLEHEADERROWSTART = '<thead><tr>';
    protected $VIEWTABLEHEADERROWEND = '</tr></thead>';
    protected $VIEWTABLEHEADERCOLSTART = '<th class="datatablehead :#:NUM:#:">';
    protected $VIEWTABLEHEADERCOLEND = '</th>';
    protected $VIEWTABLEROWSTART = '<tr>';
    protected $VIEWTABLEROWSTARTHIGHLIGHT = '<tr>';
    protected $VIEWTABLEROWEND = '</tr>';
    protected $VIEWTABLECOLSTART = '<td valign="top" :#:FLIP:#:>';
    protected $VIEWTABLECOLSTARTHIGHLIGHT = '<td valign="top" :#:FLIP:#: class="datatablerowhighlight">';
    protected $VIEWTABLECOLEND = '</td>';
    /**
     * set i18n object
     *
     * @param object $object db handle
     *
     * @return void
     */
    public function setI18n($object = null)
    {
        $this->I18N = $object;
    }
    /**
     * return mvc encoded name
     *
     * @return string
     */
    public function getMVCName()
    {
        return $this->MVCNAME;
    }
    /**
     * set a new table start for view table
     *
     * @param int $start new table start tag
     *
     * @return bool
     */
    public function viewSetTableStart ($start = null)
    {
        if (!$start)
        return false;
        $this->VIEWTABLESTART = $start;
        return true;
    }
    /**
     * torn on / off dataser browse line
     *
     * @param bool $bool false=no browse
     *
     * @return void
     */
    public function viewSetNoFoot ($bool = false)
    {
        $this->VIEWNOFOOT = $bool;
    }
    /**
     * translate a sentence with i18n object
     *
     * @param string $string string to translate
     *
     * @return string translated string
     */
    public function i18n($string = null)
    {
        if (!is_object($this->I18N))
            $this->I18N = frontend::getInstance()->getI18n();
        return $this->I18N->Translate($string);
    }
    /**
     * return translate object
     *
     * @return object
     */
    protected function getI18N()
    {
        if (!is_object($this->I18N))
            $this->I18N = frontend::getInstance()->getI18n();
        return $this->I18N;
    }
    /**
     * constructor
     *
     * @param string $name   unique mvc name
     * @param object $handle db handle
     * @param string $table  db table name
     *
     * @return void
     */
    public function mvc($name = "unique", $handle = null, $table = null)
    {
        $this->MVCNAME = md5($name);
        $this->ModelSetDBHandle($handle);
        $this->ModelSetTable($table);
        if (sendvar($this->MVCNAME . 'MVCPOSITION') !== false)
            $_SESSION[$this->MVCNAME . 'MVCPOSITION'] = sendvar($this->MVCNAME . 'MVCPOSITION');
        $this->VIEWOFFSET = isset($_SESSION[$this->MVCNAME . 'MVCPOSITION']) ?
            $_SESSION[$this->MVCNAME . 'MVCPOSITION'] : 0;
        if (sendvar($this->MVCNAME . 'MVCDIRECTION'))
            $_SESSION[$this->MVCNAME . 'MVCDIRECTION'] = sendvar($this->MVCNAME . 'MVCDIRECTION');
        $this->ORDERBYDIRECTION = isset($_SESSION[$this->MVCNAME . 'MVCDIRECTION']) ?
            $_SESSION[$this->MVCNAME . 'MVCDIRECTION'] : 'ASC';
        if (sendvar($this->MVCNAME . 'MVCSORTBY'))
            $_SESSION[$this->MVCNAME . 'MVCSORTBY'] = sendvar($this->MVCNAME . 'MVCSORTBY');
        $this->ORDERBYCOLUMN = isset($_SESSION[$this->MVCNAME . 'MVCSORTBY']) ?
            $_SESSION[$this->MVCNAME . 'MVCSORTBY'] : null;
    }
    /**
     * set a new display limit for the list
     *
     * @param int $limit quantity of records per page
     *
     * @return void
     */
    public function setDisplayLimit($limit = 20)
    {
        $this->DISPLAYLIMIT = $limit;
    }
    /**
     * get display limit
     *
     * @return int
     */
    public function getDisplayLimit()
    {
        return $this->DISPLAYLIMIT;
    }
    /**
     * Set DB Handler
     *
     * @param object $handle Handle
     *
     * @return void
     */
    public function modelSetDBHandle($handle = null)
    {
        if ($handle)
            $this->DB = $handle;
        $this->VIEWDATASETOUTPUT = null;
    }
    /**
     * Set DB filter
     *
     * @param string $filter Tablename
     *
     * @return void
     */
    public function modelSetFilter($filter = null)
    {
        if ($filter)
            $this->FILTER = $filter;
        $this->VIEWDATASETOUTPUT = null;
    }
    /**
     * Set DB table
     *
     * @param string $table Tablename
     *
     * @return void
     */
    public function modelSetTable($table = null)
    {
        if ($table)
            $this->TABLE = $table;
        $this->VIEWDATASETOUTPUT = null;
    }
    /**
     * columns to select from DB table
     *
     * @param mixed $cols array or string of col.name(s)
     *
     * @return void
     */
    public function modelSetTableColumns($cols = null)
    {
        if (is_array($cols))
            $this->DBTABLECOLUMNS = array_merge($this->DBTABLECOLUMNS, $cols);
        elseif ($cols)
            $this->DBTABLECOLUMNS[$cols] = $cols;
        $this->VIEWDATASETOUTPUT = null;
    }
    /**
     * map beauty names for cols
     *
     * @param string $col    colname
     * @param string $beauty beautified name
     *
     * @return void
     */
    public function viewBeautifyColumns($col = null, $beauty = null)
    {
        $this->BEAUTYDBTABLECOLUMNS[$col] = $beauty;
    }
    /**
     * hide a column in view
     *
     * @param mixed $cols column name as string or array with col. names e.g. field1, field2, ...
     *
     * @return void
     */
    public function viewDontShowColumns($cols = null)
    {
        if (is_array($cols))
        {
            foreach ($cols as $temp)
            {
                $this->VIEWDONTSHOWCOLUMNS[$temp] = $temp;
            }
        }
        else
            $this->VIEWDONTSHOWCOLUMNS[$cols] = $cols;
    }
    /**
     * auto sorting of col not allowed
     *
     * @param mixed $cols column name as string or array with col. names e.g. field1, field2, ...
     *
     * @return void
     */
    public function viewDontSortColumns($cols = null)
    {
        if (is_array($cols))
        {
            foreach ($cols as $temp)
            {
                $this->VIEWDONTSORTCOLUMNS[$temp] = $temp;
            }
        }
        else
            $this->VIEWDONTSORTCOLUMNS[$cols] = $cols;
    }
    /**
     * glue some to table head cols
     *
     * @param mixed  $cols  column name as string or array with col. names e.g. field1, field2, ...
     * @param string $value string to glue in
     *
     * @return void
     */
    public function viewGlueToColumn($cols = null, $value = null)
    {
        if (is_array($cols))
        {
            foreach ($cols as $k=>$v)
            {
                $this->VIEWGLUETOCOLUMNS[$k] = $v;
            }
        }
        else
            $this->VIEWGLUETOCOLUMNS[$cols] = $value;
    }
    /**
     * Prepend a column to the existing colums
     *
     * @param string $cols     column name
     * @param string $function function call when row is build
     *
     * @return void
     */
    public function viewPrependColumn($cols = null, $function = null)
    {
        $this->PREPENDCOLUMNS[$cols] = $function;
    }
    /**
     * Append a column to the existing colums
     *
     * @param string $cols     column name
     * @param string $function function call when row is build
     *
     * @return void
     */
    public function viewAppendColumn($cols = null, $function = null)
    {
        $this->APPENDCOLUMNS[$cols] = $function;
    }
    /**
     * read datas from SQL in array
     *
     * @param string $where where clause for sql statement
     *
     * @return bool true if datas could received
     */
    protected function viewFetchData($where = null)
    {
        if (!$this->DBTABLECOLUMNS || !$this->DB || !$this->TABLE)
            return false;
        $this->VIEWDATASETOUTPUT = null;
        if (is_null($this->ORDERBYCOLUMN))
            $this->ORDERBYCOLUMN = current($this->DBTABLECOLUMNS);
        // remove as definition
        $this->ORDERBYCOLUMN = preg_replace("=\sAS\s.+$=si", "", $this->ORDERBYCOLUMN);
        // find out quantity of datas
        $res = $this->ViewSetDatasTotal(
            count(
                $this->DB->SelectRows(
                    $this->DBTABLECOLUMNS,
                    $this->TABLE,
                    $where . ($this->GROUPBY ? ' GROUP BY ' . $this->GROUPBY . ' ' : '')
                )
            )
        );
        if (!$res)
            return false;
        // if a filter is set, check if filter already in session
        if ($this->FILTER)
        {
            if ($_SESSION[$this->MVCNAME . "MVCFILTER"] != md5($this->FILTER)
                || !$_SESSION[$this->MVCNAME . "MVCFILTER"]
            )
            {
                // filter is not yet in session => save
                $_SESSION[$this->MVCNAME . "MVCFILTER"] = md5($this->FILTER);
                // reset view offset
                $this->VIEWOFFSET = 0;
                $_SESSION[$this->MVCNAME . "MVCPOSITION"] = 0;
            }
        }
        else
        {
            // check if filter session exists
            if (isset($_SESSION[$this->MVCNAME . "MVCFILTER"]))
            {
                // empty filter session
                unset ($_SESSION[$this->MVCNAME . "MVCFILTER"]);
                // reset view offset
                $this->VIEWOFFSET = 0;
                $_SESSION[$this->MVCNAME . "MVCPOSITION"] = 0;
            }
        }
        $this->DATASET = $this->DB->SelectRows(
            $this->DBTABLECOLUMNS,
            $this->TABLE,
            $where . ($this->GROUPBY ? ' GROUP BY ' . $this->GROUPBY . ' ' : ''),
            $this->ORDERBYCOLUMN . " " . $this->ORDERBYDIRECTION,
            $this->VIEWOFFSET,
            $this->DISPLAYLIMIT,
            true
        );
        if (!$this->DATASET)
            return false;
        else
        {
            $this->VIEWFETCHMARKER = true;
            return true;
        }
    }
    /**
     * set sort by column and direction
     *
     * @param string $col column name
     * @param string $dir ASC / DESC
     *
     * @return void
     */
    public function viewSetSortBy($col = null, $dir = "ASC")
    {
        $this->ORDERBYCOLUMN = $col;
        $this->ORDERBYDIRECTION = $dir;
    }
    /**
     * return sort by col if set
     *
     * @return void
     */
    public function viewGetSortBy()
    {
        return $this->ORDERBYCOLUMN;
    }
    /**
     * return sort column
     *
     * @return string sort column
     */
    public function getSortBy()
    {
        if ($this->ViewGetSortBy())
            return $this->ViewGetSortBy();
        // set first row
        foreach ($this->DBTABLECOLUMNS as $col)
        {
            if (in_array($col, $this->VIEWDONTSHOWCOLUMNS))
                continue;
            $this->ViewSetSortBy($col, 'ASC');
            break;
        }
        return $this->ViewGetSortBy();
    }
    /**
     * return sort direction
     *
     * @return string sort direction ASC|DESC
     */
    public function getSortByDirection()
    {
        return ($this->ORDERBYDIRECTION ? $this->ORDERBYDIRECTION : 'ASC');
    }
    /**
     * set group by
     *
     * @param string $statement group by line
     *
     * @return void
     */
    public function viewSetGroupBy($statement = null)
    {
        $this->GROUPBY = $statement;
    }
    /**
     * set total quantity of datas
     *
     * @param int $quantity quantity
     *
     * @return int quantity
     */
    public function viewSetDatasTotal($quantity = 0)
    {
        return $this->VIEWDATASTOTAL = $quantity;
    }
    /**
     * get total quantity of datas
     *
     * @return  int   quantity
     */
    public function viewGetDatasTotal()
    {
        return $this->VIEWDATASTOTAL ? $this->VIEWDATASTOTAL : 0;
    }
    /**
     * get href target for view
     *
     * @param string $target url - target
     *
     * @return void
     */
    public function viewGetTarget($target = null)
    {
        return $this->VIEWHREF;
    }
    /**
     * set href target for view
     *
     * @param string $target url - target
     *
     * @return void
     */
    public function viewSetTarget($target = null)
    {
        $this->VIEWHREF = $target;
    }
    /**
     * set a datahandler function for a column
     *
     * @param string        $col      column name
     * @param string|object $function function to call for
     *
     * @return void
     */
    public function viewSetColumnHandler($col = null, $function = null)
    {
        $this->VIEWCOLUMNHANDLERFUNCTIONS[$col] = $function;
    }
    /**
     * set a highlight for a row that is pointed by a unique val
     *
     * @param string $col column name
     * @param string $val value of the col.
     *
     * @return void
     */
    public function highLightRow($col = null, $val = null)
    {
        $this->HIGHLIGHTROW[$col] = $val;
    }
    /**
     * start reading datas
     *
     * @return bool
     */
    public function readDataFromSource ()
    {
        return $this->viewFetchData($this->FILTER);
    }
    /**
     * create a html table from the entries
     *
     * @return string HTML Table
     */
    public function viewDataSet()
    {
        if (!$this->VIEWFETCHMARKER)
            if (!$this->readDataFromSource())
                return false;
        if (!$this->DATASET)
            return false;
        if ($this->VIEWDATASETOUTPUT)
            return $this->VIEWDATASETOUTPUT;
        // empty array with displayed highlighted rows
        $playedhighlighted = array ();
        $output = str_replace(':#:MVCID:#:', $this->MVCNAME, $this->VIEWTABLESTART);
        $int_cols = 1;
        // build header
        $output .= $this->VIEWTABLEHEADERROWSTART;
        // manual prep. cols
        foreach ($this->PREPENDCOLUMNS as $k => $v)
        {
            $output .= str_replace(':#:NUM:#:', $int_cols, $this->VIEWTABLEHEADERCOLSTART);
            $int_cols++;
            $output .= $k ? $k : "&nbsp;";
            $output .= $this->VIEWTABLEHEADERCOLEND;
        }
        foreach ($this->DATASET[0] as $k => $v)
        {
            if (in_array($k, $this->VIEWDONTSHOWCOLUMNS))
                continue;
            $output .= str_replace(':#:NUM:#:', $int_cols, $this->VIEWTABLEHEADERCOLSTART);
            $int_cols++;
            // no sorting
            if (!in_array($k, $this->VIEWDONTSORTCOLUMNS))
                $output .= '<a href="' . $this->VIEWHREF . '&' . $this->MVCNAME . 'MVCPOSITION='
                    . sendvar($this->MVCNAME . "MVCPOSITION") . '&' . $this->MVCNAME . 'MVCSORTBY=' . $k
                    . '&' . $this->MVCNAME . 'MVCDIRECTION=' . ($this->ORDERBYDIRECTION == "ASC" ? "DESC" : "ASC")
                    . '" OnClick="_body_onunload();">';

            $output .= (isset ($this->BEAUTYDBTABLECOLUMNS[$k]) ? $this->BEAUTYDBTABLECOLUMNS[$k] : $k);
            $output .= "&nbsp;";

            // no sorting
            if (!in_array($k, $this->VIEWDONTSORTCOLUMNS))
                $output .= "</a>";
            if ($k == $this->ORDERBYCOLUMN)
                $output .= ($this->ORDERBYDIRECTION == "ASC" ? ASCGRAPH : DESCGRAPH);
            if (!in_array($k, $this->VIEWGLUETOCOLUMNS))
                $output .= $this->VIEWGLUETOCOLUMNS[$k];
            $output .= $this->VIEWTABLEHEADERCOLEND;
        }
        // manual ap. cols
        foreach ($this->APPENDCOLUMNS as $k => $v)
        {
            $output .= str_replace(':#:NUM:#:', $int_cols, $this->VIEWTABLEHEADERCOLSTART);
            $int_cols++;
            $output .= $k;
            $output .= $this->VIEWTABLEHEADERCOLEND;
        }
        $output .= $this->VIEWTABLEHEADERROWEND;
        // build body
        reset($this->DATASET);
        $flip = false;
        // set placeholder for selected ds
        $output .= "<tbody>#PLACEHOLDER#";
        foreach ($this->DATASET as $object)
        {
            $highlight = false;
            // is this highlighted
            foreach ($object as $k => $v)
            {
                if (isset($this->HIGHLIGHTROW[$k]) && $this->HIGHLIGHTROW[$k] == $v)
                {
                    $highlight = true;
                    $flip = true;
                    $playedhighlighted[$k] = $v;
                }
            }
            if ($highlight)
                $output .= $this->VIEWTABLEROWSTARTHIGHLIGHT;
            else
                $output .= $this->VIEWTABLEROWSTART;
            // man. prep. cols
            foreach ($this->PREPENDCOLUMNS as $k => $v)
            {
                $output .= $highlight ? $this->VIEWTABLECOLSTARTHIGHLIGHT : $this->VIEWTABLECOLSTART;
                $output .= call_user_func($v, $this, $object);
                $output .= $this->VIEWTABLECOLEND;
            }
            foreach ($object as $k => $v)
            {
                if (in_array($k, $this->VIEWDONTSHOWCOLUMNS))
                    continue;
                $output .= $highlight ? $this->VIEWTABLECOLSTARTHIGHLIGHT : $this->VIEWTABLECOLSTART;
                $output .= $this->VIEWCOLUMNHANDLERFUNCTIONS[$k] ?
                    call_user_func($this->VIEWCOLUMNHANDLERFUNCTIONS[$k], $this, $v, $object) : HtmlFormat($v);
                $output .= $this->VIEWTABLECOLEND;
            }
            // man. app. cols
            foreach ($this->APPENDCOLUMNS as $k => $v)
            {
                $output .= $highlight ? $this->VIEWTABLECOLSTARTHIGHLIGHT : $this->VIEWTABLECOLSTART;
                $output .= call_user_func($v, $this, $object);
                $output .= $this->VIEWTABLECOLEND;
            }
            $output .= $this->VIEWTABLEROWEND;
            $output = str_replace(":#:FLIP:#:", $flip ? "" : 'class="datatableflip"', $output);
            $flip = !$flip;
        }
        // build data navigation
        $output .= '</tbody>' . $this->ViewDataNavigation();
        $output .= $this->VIEWTABLEEND;
        // calcultate undisplayd but highlighted rows
        // yet to show selected sets
        $playyet = array_diff_key($this->HIGHLIGHTROW, $playedhighlighted);
        $this->VIEWDATASETOUTPUT = str_replace("#PLACEHOLDER#", $this->getHighlightedRows($playyet), $output);
        return $this->VIEWDATASETOUTPUT;
    }
    /**
     * gather highlighted Datasets
     *
     * @param array $playyet cols to show
     *
     * @return string table elements with selected datasets or empty string
     */
    protected function getHighlightedRows ($playyet = array ())
    {
        if (!count($playyet))
            return ''; // nothing to show
        // gather rest of datas
        $output = $where = (string) null;
        $flip = true;
        foreach ($playyet as $k=>$v)
        {
            if ($where)
            $where .= " OR ";
            $where .= "" . $k . "= '" . $v . "'";
        }
        $temp = $this->DB->SelectRows(
            $this->DBTABLECOLUMNS,
            $this->TABLE,
            $where . ($this->GROUPBY ? ' GROUP BY ' . $this->GROUPBY . ' ' : ''),
            $this->ORDERBYCOLUMN . " " . $this->ORDERBYDIRECTION,
            null,
            null,
            true
        );
        if (!$temp)
            return '';
        foreach ($temp as $object)
        {
            $highlight = true;
            if ($highlight)
                $output .= $this->VIEWTABLEROWSTARTHIGHLIGHT;
            else
                $output .= $this->VIEWTABLEROWSTART;
            // man. prep. cols
            foreach ($this->PREPENDCOLUMNS as $k => $v)
            {
                $output .= $highlight ? $this->VIEWTABLECOLSTARTHIGHLIGHT : $this->VIEWTABLECOLSTART;
                $output .= call_user_func($v, $this, $object);
                $output .= $this->VIEWTABLECOLEND;
            }
            foreach ($object as $k => $v)
            {
                if (in_array($k, $this->VIEWDONTSHOWCOLUMNS))
                    continue;
                $output .= $highlight ? $this->VIEWTABLECOLSTARTHIGHLIGHT : $this->VIEWTABLECOLSTART;
                $output .= $this->VIEWCOLUMNHANDLERFUNCTIONS[$k] ?
                    call_user_func($this->VIEWCOLUMNHANDLERFUNCTIONS[$k], $this, $v, $object) : HtmlFormat($v);
                $output .= $this->VIEWTABLECOLEND;
            }
            // man. app. cols
            foreach ($this->APPENDCOLUMNS as $k => $v)
            {
                $output .= $highlight ? $this->VIEWTABLECOLSTARTHIGHLIGHT : $this->VIEWTABLECOLSTART;
                $output .= call_user_func($v, $this, $object);
                $output .= $this->VIEWTABLECOLEND;
            }
            $output .= $this->VIEWTABLEROWEND;
            $output = str_replace(":#:FLIP:#:", $flip ? "" : 'class="datatableflip"', $output);
        }
        return $output;
    }
    /**
     * create navigation for data view
     *
     * @return string HTML Table
     */
    public function viewDataNavigation()
    {
        if ($this->VIEWNOFOOT)
            return '';
        $output = (string) null;
        $output .= '<tfoot>' . $this->VIEWTABLEROWSTART;
        $iQty = count($this->PREPENDCOLUMNS)
            + count($this->DBTABLECOLUMNS)
            + count($this->APPENDCOLUMNS)
            - count($this->VIEWDONTSHOWCOLUMNS);
        $output .= preg_replace("=<td=s", '<td colspan="' . ($iQty) . '" align="center"', $this->VIEWTABLECOLSTART);
        $output .= '<div style="float:left;"><b>'.$this->getI18N()->translate("Datas total").':</b> '
            . $this->VIEWDATASTOTAL.'</div>';
        $offsets = BrowseDatas($this->VIEWDATASTOTAL, $this->DISPLAYLIMIT, $this->VIEWOFFSET);
        $browse = (string) null;
        foreach ($offsets as $k => $v)
        {
            if ($this->VIEWOFFSET == $v)
                $browse .= '<b style="font-size: 130%;">' . $k . '</b> ';
            else
                $browse .= '<a href="' . $this->VIEWHREF . '&' . $this->MVCNAME . 'MVCPOSITION=' . $v . '&'
                    . $this->MVCNAME . 'MVCSORTBY=' . $this->ORDERBYCOLUMN . '&'
                    . $this->MVCNAME . 'MVCDIRECTION=' . $this->ORDERBYDIRECTION . '" OnClick="_body_onunload();">'
                    . $k . '</a> ';
        }
        $output .= $browse . $this->VIEWTABLECOLEND;
        $output .= $this->VIEWTABLEROWEND . '</tfoot>';
        return $output;
    }
}

/**
 * create insert and update forms based on db Table properties
 *
 * @category  PHP
 * @package   PHPMY
 * @author    Sven Ruttloff, www.sruttloff.de <service@sruttloff.de>
 * @copyright 2010 Sven Ruttloff, www.sruttloff.de
 * @license   http://www.sruttloff.de/cms/files/licence_en_lgpl.txt LGPL
 * @link      phpmy
 */
class dbTableController
{
    /**
     * @var  array  field that have failed by submission
     */
    public $FAILUREFIELD = array ();
    /**
     * @var  array  user function to handle field
     */
    public $CONTROLHANDLERFUNCTIONS = array ();
    /**
     * @var  array  user function to check fields after submit
     */
    public $CONTROLHANDLERFUNCTIONSSUBMITTED = array ();
    /**
     * @var  array  cols and vals to save in table
     */
    public $SAVECOLS = array ();
    /**
     * @var  object  called by object
     */
    public $PARENT = null;
    /**
     * @var  string  form template
     */
    public $FORMTEMPLATE = null;
    /**
     * @var  array  columns to hide from form
     */
    public $HIDE = array ();
    /**
     * @var  array  not required fields
     */
    public $NOTREQUIREDFIELDS = array ();
    /**
     * @var  string  text for create form submit button
     */
    public $CREATEBUTTON = "CREATE ENTRY";
    /**
     * @var  string  text for required fields
     */
    public $REQUIREDMARKER = '<span style="font-size:80%;color:red;font-weight:bold;">*</span>';
    /**
     * @var   string  href target for the control
     */
    public $CONTROLHREF = "";
    /**
     * @var   string  translation engine with ->Get() methode
     */
    public $I18N = null;
    /**
     * @var   string  table name to operate on
     */
    public $TABLE = null;
    /**
     * set i18n object
     *
     * @param object $object i18n object
     *
     * @return void
     */
    public function seti18n($object = null)
    {
        $this->I18N = $object;
    }
    /**
     * Set DB table
     *
     * @param string $table Tablename
     *
     * @return void
     */
    public function setTable($table = null)
    {
        if ($table)
            $this->TABLE = $table;
    }
    /**
     * translate a string with i18n
     *
     * @param string $string string to translate
     *
     * @return string translated string
     */
    public function i18n($string = null)
    {
        if (!is_object($this->I18N))
        {
            // try to get parent object
            if (is_object($this->PARENT->I18N))
                $this->I18N = $this->PARENT->I18N;
        }
        if (!is_object($this->I18N))
            return $string;
        else
            return $this->I18N->Get($string);
    }
    /**
     * create a form to add datas in DB
     *
     * @param object $parent calling object
     *
     * @return string HTML Form
     */
    public function dbTableController($parent = null)
    {
        $this->PARENT = $parent;
        $this->CONTROLHREF = $this->PARENT->VIEWHREF;
    }
    /**
     * return encoded mvc name
     *
     * @return string mvcname
     */
    public function mvcName()
    {
        return $this->PARENT->MVCNAME;
    }
    /**
     * create a form to add datas in DB
     *
     * @param string $fieldname field is not requ.
     *
     * @return void
     */
    public function notRequired($fieldname = null)
    {
        $this->NOTREQUIREDFIELDS[$fieldname] = $fieldname;
    }
    /**
     * set a column to hide in form
     *
     * @param string $column column name to hide in form
     *
     * @return void
     */
    public function hide($column = null)
    {
        return $this->HIDE[$column] = $column;
    }
    /**
     * Set a form template
     *
     * @param string $template form template
     *
     * @return bool
     */
    public function setFormTemplate($template = null)
    {
        return $this->FORMTEMPLATE = $template;
    }
    /**
     * create a form to add datas in DB
     *
     * @return string HTML Form
     */
    public function createForm()
    {
        $colarray = array ();
        // fetch DB table properties
        $dbproperties = new DBProperties($this->PARENT->DB, $this->TABLE);
        $colarray = $dbproperties->GetColumns($this->TABLE);
        // hide autoinc. cols in form
        foreach ($colarray as $field)
        {
            if (strtolower($dbproperties->GetProperty($this->TABLE, $field, "Extra")) == "auto_increment")
                $this->Hide($field);
        }
        if (!$this->FORMTEMPLATE)
            $this->BuildForm($colarray);
        return $this->ParseForm($this->FORMTEMPLATE, $dbproperties);
    }
    /**
     * parse a db field type
     *
     * @param string $type field type
     *
     * @return string well formed type
     */
    public function parseType($type = null)
    {
        // extract type
        preg_match("=^(\w+)\((\S+)\)=", $type, $token);
        if (!$token)
            return array (1 => $type);
        unset ($token[0]);
        return $token;
    }
    /**
     * replaces placeholders in given sting against form elements
     *
     * @param string $template     form template
     * @param object $dbproperties handler for field properties
     *
     * @return string HTML Form
     */
    public function parseForm($template = null, $dbproperties = null)
    {
        $colarray = $dbproperties->GetColumns($this->TABLE);
        // hide autoinc. cols in form
        foreach ($colarray as $field)
        {
            if (in_array($field, $this->HIDE))
                continue;
            // call user function to operate field
            if ($this->CONTROLHANDLERFUNCTIONS[$field])
            {
                $template = str_replace(
                    ":#:" . $field . ":#:",
                    call_user_func($this->CONTROLHANDLERFUNCTIONS[$field], $this, $dbproperties),
                    $template
                );
                continue;
            }
            $type = $this->ParseType(strtoupper($dbproperties->GetProperty($this->TABLE, $field, 'Type')));
            // var_dump($type);
            switch ($type[1]) {
                case "VARCHAR" :
                case "INT" :
                case "DECIMAL" :
                case "FLOAT" :
                    $array = array (
                        "name" => $this->PARENT->MVCNAME . $field,
                        "value" => (
                            sendvar($this->PARENT->MVCNAME . $field) !== false ?
                            sendvar($this->PARENT->MVCNAME . $field) :
                            $dbproperties->GetProperty($this->TABLE, $field, "Default")
                        ),
                        "maxlength" => $type[2]
                    );
                    $template = str_replace(
                        ":#:" . $field . ":#:",
                        FormInput($array, false, $this->FAILUREFIELD[$field]),
                        $template
                    );
                    break;
                case "TEXT" :
                    $array = array (
                        "name" => $this->PARENT->MVCNAME . $field,
                        "value" => sendvar($this->PARENT->MVCNAME . $field)
                    );
                    $template = str_replace(
                        ":#:" . $field . ":#:",
                        FormTextarea($array, false, $this->FAILUREFIELD[$field]),
                        $template
                    );
                    break;
                case "ENUM" :
                    // fetch all values
                    preg_match_all("='(.+)'=sU", $type[2], $vals);
                    $selvals = array ();
                    foreach ($vals[1] as $values)
                    {
                        $selvals[$values] = $values;
                    }
                    $array = array ("name" => $this->PARENT->MVCNAME . $field);
                    $template = str_replace(
                        ":#:" . $field . ":#:",
                        FormSelect(
                            $array,
                            $selvals,
                            (
                                sendvar($this->PARENT->MVCNAME . $field) !== false ?
                                array (sendvar($this->PARENT->MVCNAME . $field)) :
                                array ($dbproperties->getProperty($this->TABLE, $field, "Default"))
                            ),
                            $this->FAILUREFIELD[$field]
                        ),
                        $template
                    );
                    break;
                default :
                    $array = array (
                      "name" => $this->PARENT->MVCNAME . $field,
                      "value" => sendvar($this->PARENT->MVCNAME . $field),
                      "maxlength" => $type[2]
                    );
                    $template = str_replace(
                        ":#:" . $field . ":#:",
                        FormInput($array, false, $this->FAILUREFIELD[$field]),
                        $template
                    );
                    break;
            }
        }
        $array = array (
           "type" => "submit",
           "value" => $this->CREATEBUTTON
        );
        return str_replace(
            ":#:POSTTARGET:#:",
            $this->CONTROLHREF,
            str_replace(":#:SUBMITBUTTON:#:", FormInput($array), $template)
        );
    }
    /**
     * build a form template with placeholders
     *
     * @param array $array column names
     *
     * @return string HTML Form
     */
    public function buildForm($array = array ())
    {
        if ($this->FORMTEMPLATE)
        return $this->FORMTEMPLATE;
        $form = '<form class="mvcform" METHOD="POST" NAME="' . $this->PARENT->MVCNAME
            . '" action=":#:POSTTARGET:#:"  OnSubmit="_body_onunload();">:#:HIDDENFIELDS:#:';
        $form .= '<table cellspacing="2" cellpadding="0" border="0" style="max-width:100%;">';
        foreach ($array as $k)
        {
            if (in_array($k, $this->HIDE))
                continue;
            $form .= '<tr>';
            $form .= '<td valign="top" class="mvclabel">'
                . (!in_array($k, $this->NOTREQUIREDFIELDS) ? $this->REQUIREDMARKER . " " : "")
                . (isset ($this->PARENT->BEAUTYDBTABLECOLUMNS[$k]) ? $this->PARENT->BEAUTYDBTABLECOLUMNS[$k] : $k)
                . ' :</td>';
            $form .= '<td valign="top">:#:' . $k . ':#:</td>';
            $form .= '</tr>';
        }
        $form .= '<tr>';
        $form .= '<td colspan="2" align="left">' . $this->REQUIREDMARKER . ' <span class="requiredinfo">'
            . $this->i18n("These fields are required!") . '</span></td>';
        $form .= '</tr>';
        $form .= '<tr>';
        $form .= '<td colspan="2" align="center">:#:SUBMITBUTTON:#:</td>';
        $form .= '</tr>';
        $form .= '</table>';
        $form .= '</form>';
        $this->FORMTEMPLATE = $form;
        return $this->FORMTEMPLATE;
    }
    /**
     * checks if all required fields are filled
     *
     * @return bool
     */
    public function checkRequiredFields()
    {
        $colarray = $this->TABLEPROPERTIES->GetColumns($this->TABLE);
        $result = true;
        foreach ($colarray as $field)
        {
            if (sendvar($this->PARENT->MVCNAME . $field) !== false || $this->CONTROLHANDLERFUNCTIONSSUBMITTED[$field])
            {
                $this->SAVECOLS[$field] = sendvar($this->PARENT->MVCNAME . $field);
                if ($this->CONTROLHANDLERFUNCTIONSSUBMITTED[$field])
                {
                    $temp = false;
                    $temp = call_user_func(
                        $this->CONTROLHANDLERFUNCTIONSSUBMITTED[$field], $this, sendvar($this->PARENT->MVCNAME . $field)
                    );
                    if ($temp === false)
                    {
                        $result = false;
                        $this->FAILUREFIELD[$field] = true;
                    }
                    $this->SAVECOLS[$field] = sendvar($this->PARENT->MVCNAME . $field);
                    continue;
                }
            }
            if (in_array($field, $this->NOTREQUIREDFIELDS))
                continue;
            if (sendvar($this->PARENT->MVCNAME . $field) == '')
            {
                $result = false;
                $this->FAILUREFIELD[$field] = true;
            }
        }
        return $result;
    }
    /**
     * set a datahandler function for a field
     * forward $this, $dbproperties
     *
     * @param string $field    field name
     * @param string $function function to call for
     *
     * @return void
     */
    public function setFieldHandler($field = null, $function = null)
    {
        $this->CONTROLHANDLERFUNCTIONS[$field] = $function;
    }
    /**
     * set a datahandler function for a field when form is submitted
     * forwards: $this,  sendvar($this->PARENT->MVCNAME . $field)
     *
     * @param string $field    field name
     * @param string $function function to call for
     *
     * @return void
     */
    public function setFieldHandlerSubmitted($field = null, $function = null)
    {
        $this->CONTROLHANDLERFUNCTIONSSUBMITTED[$field] = $function;
    }
}

/**
 * insert datas into DB table
 *
 * @category  PHP
 * @package   PHPMY
 * @author    Sven Ruttloff, www.sruttloff.de <service@sruttloff.de>
 * @copyright 2010 Sven Ruttloff, www.sruttloff.de
 * @license   http://www.sruttloff.de/cms/files/licence_en_lgpl.txt LGPL
 * @link      phpmy
 */
class mvcInsert extends dbTableController
{
    /**
     * @var   bool  true if form was submitted
     */
    public $SUBMITTED = false;
    /**
     * @var   bool  insertes AI val if datas was successfull inserted
     */
    public $RESULT = false;
    /**
     * @var   string  function to call before insert
     */
    public $BEFOREINSERT = null;
    /**
     * constructor
     *
     * @param object $parent    parent object (mvc)
     * @param string $tablename table name where to insert
     *
     * @return void
     */
    public function mvcInsert($parent = null, $tablename = null)
    {
        $this->PARENT = $parent;
        if (is_null($tablename))
            $this->TABLE = $this->PARENT->TABLE;
        else
            $this->TABLE = $tablename;
        $this->CONTROLHREF = $this->PARENT->VIEWHREF;
        // fetch DB table properties
        $this->TABLEPROPERTIES = new DBProperties($this->PARENT->DB, $this->TABLE);
        $colarray = $this->TABLEPROPERTIES->GetColumns($this->TABLE);
        // hide autoinc. cols in form
        foreach ($colarray as $field)
        {
            if (strtolower($this->TABLEPROPERTIES->GetProperty($this->TABLE, $field, "Extra")) == "auto_increment")
            {
                $this->Hide($field);
                $this->NotRequired($field);
            }
        }
    }
    /**
     * set a datahandler function before insert will be done
     *
     * @param string $function function to call for
     *
     * @return void
     */
    public function setInsertBeforeHandler($function = null)
    {
        $this->BEFOREINSERT = $function;
    }
    /**
     * insert datas into db
     *
     * @return bool
     */
    public function action()
    {
        // form submitted
        if (sendvar($this->PARENT->MVCNAME . "mvcadd") == $this->PARENT->MVCNAME)
            $this->SUBMITTED = true;
        else
            return $this->SUBMITTED;
        if ($this->CheckRequiredFields())
            $this->RESULT = true;
        else
            return $this->SUBMITTED;
        // insert datas
        // something to do before insert
        if ($this->BEFOREINSERT)
            $this->SAVECOLS = call_user_func($this->BEFOREINSERT, $this->SAVECOLS);
        if ($this->SAVECOLS)
        {
            if (!$this->PARENT->DB->InsertRow($this->SAVECOLS, $this->TABLE))
                $this->RESULT = false;
            else
                $this->RESULT = $this->PARENT->DB->insert_id();
        }
        else
            $this->RESULT = false;
        return $this->SUBMITTED;
    }
    /**
     * if insert is done succ. and the autoincr.val is available => return id
     *
     * @return int  autoincrement from last insert
     */
    public function getInsertId ()
    {
        return $this->RESULT;
    }
    /**
     * create a form to add datas in DB
     *
     * @return string HTML Form
     */
    public function createForm()
    {
        $colarray = $this->TABLEPROPERTIES->GetColumns($this->TABLE);
        $array = array (
           "name" => $this->PARENT->MVCNAME . "mvcadd",
           "type" => "hidden",
           "value" => $this->PARENT->MVCNAME
        );
        return str_replace(
            ":#:HIDDENFIELDS:#:",
            FormInput($array),
            $this->ParseForm($this->BuildForm($colarray), $this->TABLEPROPERTIES)
        );
    }
}
/**
 * update datas in DB table
 *
 * @category  PHP
 * @package   PHPMY
 * @author    Sven Ruttloff, www.sruttloff.de <service@sruttloff.de>
 * @copyright 2010 Sven Ruttloff, www.sruttloff.de
 * @license   http://www.sruttloff.de/cms/files/licence_en_lgpl.txt LGPL
 * @link      phpmy
 */
class mvcUpdate extends dbTableController
{
    /**
     * @var   bool  true if form was submitted
     */
    public $SUBMITTED = false;
    /**
     * @var   bool  true if datas was successfull inserted
     */
    public $RESULT = false;
    /**
     * @var   array  source column and unique value to update
     */
    public $UPDATECOLUMN = array ();
    protected $BEFOREUPDATE = null;
    /**
     * Update component
     *
     * @param object $parent    parent object (us. mvc)
     * @param array  $source    source column and value
     * @param string $tablename table name
     *
     * @return void
     */
    public function mvcUpdate($parent = null, $source = array (), $tablename = null)
    {
        $this->PARENT = $parent;
        $this->UPDATECOLUMN = $source;
        if (is_null($tablename))
            $this->TABLE = $this->PARENT->TABLE;
        else
            $this->TABLE = $tablename;
        $this->CREATEBUTTON = $this->i18n("UPDATE ENTRY");
        $this->CONTROLHREF = $this->PARENT->VIEWHREF;
        // fetch DB table properties
        $this->TABLEPROPERTIES = new DBProperties($this->PARENT->DB, $this->TABLE);
        $colarray = $this->TABLEPROPERTIES->GetColumns($this->TABLE);
        // hide autoinc. cols in form
        foreach ($colarray as $field)
        {
            if (strtolower($this->TABLEPROPERTIES->GetProperty($this->TABLE, $field, "Extra")) == "auto_increment")
            {
                $this->Hide($field);
                $this->NotRequired($field);
            }
        }
    }
    /**
     * update datas in db table
     *
     * @return bool
     */
    public function action()
    {
        // form submitted
        if (sendvar($this->PARENT->MVCNAME . "mvcupdate") == $this->PARENT->MVCNAME)
            $this->SUBMITTED = true;
        else
        {
            // load values from db into sendvars
            $preload = $this->PARENT->DB->SelectRows(
                $this->TABLEPROPERTIES->GetColumns($this->TABLE),
                $this->TABLE,
                key($this->UPDATECOLUMN) . "='" . $this->UPDATECOLUMN[key($this->UPDATECOLUMN)] . "'"
            );
            foreach ($preload as $field => $value)
            {
                sendvar_set($this->PARENT->MVCNAME . $field, $value);
            }
            return $this->SUBMITTED;
        }
        if ($this->CheckRequiredFields())
            $this->RESULT = true;
        else
            return $this->SUBMITTED;
        // something to do before update
        if ($this->BEFOREUPDATE)
            $this->SAVECOLS = call_user_func($this->BEFOREUPDATE, $this->SAVECOLS);
        // insert datas
        $res = $this->PARENT->DB->UpdateRows(
            $this->SAVECOLS,
            $this->TABLE,
            key($this->UPDATECOLUMN) . "='" . $this->UPDATECOLUMN[key($this->UPDATECOLUMN)] . "'"
        );
        if (!$res)
            $this->RESULT = false;
        return $this->SUBMITTED;
    }
    /**
     * set a datahandler function before update will be done
     *
     * @param string $function function to call for
     *
     * @return void
     */
    public function setUpdateBeforeHandler($function = null)
    {
        $this->BEFOREUPDATE = $function;
    }
    /**
     * create a form to update datas in DB
     *
     * @return string HTML Form
     */
    public function createForm()
    {
        $colarray = $this->TABLEPROPERTIES->GetColumns($this->TABLE);
        $array = array (
           "name" => $this->PARENT->MVCNAME . "mvcupdate",
           "type" => "hidden",
           "value" => $this->PARENT->MVCNAME
        );
        $temp = str_replace(
            ":#:HIDDENFIELDS:#:",
            FormInput($array) . ":#:HIDDENFIELDS:#:",
            $this->ParseForm($this->BuildForm($colarray), $this->TABLEPROPERTIES)
        );
        $array = array (
           "name" => $this->PARENT->MVCNAME . "mvcupdatecolumn",
           "type" => "hidden",
           "value" => key($this->UPDATECOLUMN)
        );
        $temp = str_replace(":#:HIDDENFIELDS:#:", FormInput($array) . ":#:HIDDENFIELDS:#:", $temp);
        $array = array (
           "name" => $this->PARENT->MVCNAME . "mvcupdatecolumnvalue",
           "type" => "hidden",
           "value" => $this->UPDATECOLUMN[key($this->UPDATECOLUMN)]
        );
        return str_replace(":#:HIDDENFIELDS:#:", FormInput($array), $temp);
    }
    /**
     * set href target for update
     *
     * @param string $target url - target
     *
     * @return  void
     */
    public function updateSetTarget($target = null)
    {
        $this->CONTROLHREF = $target;
    }
}

/**
 * read DB Table properties and offer it
 *
 * @category  PHP
 * @package   PHPMY
 * @author    Sven Ruttloff, www.sruttloff.de <service@sruttloff.de>
 * @copyright 2010 Sven Ruttloff, www.sruttloff.de
 * @license   http://www.sruttloff.de/cms/files/licence_en_lgpl.txt LGPL
 * @link      phpmy
 */
class dbProperties
{
    /**
     * @var  object  db handle
     */
    public $DB = null;
    /**
     * @var  array  Assoc. array with table properties
     */
    public $RESULTS = array ();
    /**
     * constructor
     *
     * @param object $dbhandle db handle
     * @param string $table    table to analyze
     *
     * @return  void
     */
    public function dbProperties($dbhandle = null, $table = null)
    {
        $this->DB = $dbhandle;
        if (!is_null($table))
            $this->AnalyzeTable($table);
    }
    /**
     * read properties from a table ad store it in local array
     *
     * @param string $table table to analyze
     *
     * @return bool true if successfull done
     */
    public function analyzeTable($table = null)
    {
        if (is_null($table))
            return false;
        $query = "SHOW COLUMNS FROM " . $table . ";";
        if (!$result = $this->DB->query($query))
            return false;
        while ($col = $this->DB->fetch($result))
        {
            $this->RESULTS[$table][$col->Field]['Field'] = $col->Field;
            $this->RESULTS[$table][$col->Field]['Type'] = $col->Type;
            // trick for php code sniffer alert
            $null = 'Null';
            $this->RESULTS[$table][$col->Field]['Null'] = $col->$null;
            $this->RESULTS[$table][$col->Field]['Key'] = $col->Key;
            $this->RESULTS[$table][$col->Field]['Default'] = $col->Default;
            $this->RESULTS[$table][$col->Field]['Extra'] = $col->Extra;
        }
        return true;
    }
    /**
     * returns all cols. by name as array
     *
     * @param string $table table name
     *
     * @return array column names
     */
    public function getColumns($table = null)
    {
        $array = array ();
        foreach ($this->RESULTS[$table] as $k => $v)
        {
            $array[$k] = $k;
        }
        return $array;
    }
    /**
     * returns properties of a column
     *
     * @param string $table table name
     * @param string $col   column name
     * @param string $prop  property key
     *
     * @return mixed  property value of col.
     */
    public function getProperty($table = null, $col = null, $prop = null)
    {
        return $this->RESULTS[$table][$col][$prop];
    }
}